---
title: "SQL-SRCH-01: Provider search"
summary: Search provider locations by name, address, tax ID, and other optional criteria
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-SRCH-01 Provider search

Performs a dynamically assembled provider search across `PROVIDER_LOCATION` and its related
tables, joining tax ID, identity, service, and MCO plan tables only when the caller supplies the
corresponding search criteria, and unioning a registered-name match branch for business entities.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-SRCH-01 |
| **Operation type** | Read |
| **Execution method** | Dynamically assembled (const fragments + conditional predicates, all bind-parameterized) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | N/A (Application layer) |
| **Criteria property** | N/A |
| **Lifecycle method** | N/A |
| **Application class** | `clsProviderSearch` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:BusinessFlag` | Input | String | 1 | `'Y'` for BUSINESS, `'N'` for PERSON; omitted when search type is ALL |
| `:TaxIDNumber` | Input | String | 9 | Optional join predicate on `PROVIDER_TAX_ID.TAX_ID` |
| `:OtherID` | Input | String | 16 | Optional join predicate on `RTRIM(PROVIDER_IDENTITY.IDENTITY_VALUE)` |
| `:ServiceType` | Input | String | 5 | Optional join predicate on `TRIM(PROVIDER_SERVICE.PROVIDER_SERVICE_CODE)` |
| `:MCOPlan` | Input | String | 2 | Optional join predicate on `MCO_PROVIDER.MCO_PLAN_ID` |
| `:ProviderName` | Input | String | 50 | Value has `"%"` appended; matched with `LIKE` against upper-case name columns |
| `:ProviderType` | Input | String | 5 | Optional `pl.PROVIDER_TYPE_CODE` equality predicate |
| `:StreetAddress` | Input | String | 40 | Value has `"%"` appended; `LIKE` against `TRIM(a1.ADDRESS_LINE1)` |
| `:CityName` | Input | String | 25 | Equality against `TRIM(a1.CITY_NAME)` |
| `:StateCode` | Input | String | 2 | Equality against `a1.STATE_CODE` |
| `:ZipCode` | Input | String | 5 | Equality against `a1.ZIP_CODE` |
| `:PhoneAreaCode` | Input | String | 3 | Equality against `pl.PHONE_AREA_CODE` |
| `:PhoneNumber` | Input | String | 7 | `-` and `.` stripped, `"%"` appended; `LIKE` against `pl.PHONE_NUM` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | SELECT | `PROVIDER_LOCATION_SEQ_NUM`, `PRIMARY_LOCATION_FLAG`, `BUSINESS_FLAG`, `BUSINESS_NAME`, `LAST_NAME`, `FIRST_NAME`, `MIDDLE_NAME`, `PHONE_AREA_CODE`, `PHONE_NUM`, `PROVIDER_TYPE_CODE` |
| [PROVIDER_TYPE_CODE](../database-tables.md#provider_type_code) | JOIN | `CODE`, `CODE_DESCR` |
| [PROVIDER_ADDRESS](../database-tables.md#provider_address) | JOIN (LEFT OUTER, optional) | `ADDRESS_LINE1`, `CITY_NAME`, `STATE_CODE`, `ZIP_CODE`, `PROVIDER_ADDRESS_CODE`, `EXP_DATE` |
| [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) | JOIN (optional) | `PROVIDER_SEQ_NUM`, `TAX_ID`, `REGISTERED_NAME`, `REGISTERED_NAME_UPPER_CASE` |
| [PROVIDER_IDENTITY](../database-tables.md#provider_identity) | JOIN (optional) | `PROVIDER_LOCATION_SEQ_NUM`, `IDENTITY_VALUE` |
| [PROVIDER_SERVICE](../database-tables.md#provider_service) | JOIN (optional) | `PROVIDER_LOCATION_SEQ_NUM`, `PROVIDER_SERVICE_CODE` |
| [MCO_PROVIDER](../database-tables.md#mco_provider) | JOIN (optional) | `PROVIDER_LOCATION_SEQ_NUM`, `MCO_PLAN_ID` |

## Sample SQL

Fully assembled example with the registered-name UNION branch active and every optional
predicate applied. Optional joins (`TIN`, `ID`, `SVC`, `MP`) and each `AND` predicate are
appended only when the corresponding search field is populated.

```sql
-- Provider search
SELECT
  pl.PROVIDER_LOCATION_SEQ_NUM AS provLoc,
  pl.PRIMARY_LOCATION_FLAG     AS primaryLoc,
  pl.BUSINESS_FLAG,
  PL.BUSINESS_NAME,
  TYPE.CODE_DESCR              AS typeDescr,
  a1.ADDRESS_LINE1             AS physADDRESS,
  a1.CITY_NAME                 AS physCITY,
  a1.STATE_CODE                AS physState,
  a1.ZIP_CODE                  AS physZip,
  CASE pl.BUSINESS_FLAG
    WHEN 'N' THEN NVL(RTRIM(pl.LAST_NAME), '') || ', '
                  || NVL(RTRIM(pl.FIRST_NAME), '') || ' '
                  || NVL(RTRIM(pl.MIDDLE_NAME), '')
    ELSE RTRIM(pl.BUSINESS_NAME)
  END AS fullNAME,
  CASE NVL(RTRIM(PL.PHONE_NUM) || ' ', ' ')
    WHEN ' ' THEN ''
    ELSE pl.PHONE_AREA_CODE || '.' || substr(pl.PHONE_NUM, 1, 3)
         || '.' || substr(pl.PHONE_NUM, 4)
  END AS PHONE
FROM provider_location PL
  JOIN provider_type_code TYPE
    ON PL.PROVIDER_TYPE_CODE = TYPE.CODE
  LEFT OUTER JOIN provider_address a1
    ON (pl.PROVIDER_LOCATION_SEQ_NUM = a1.PROVIDER_LOCATION_SEQ_NUM
        AND a1.PROVIDER_ADDRESS_CODE = 'PHY'
        AND a1.EXP_DATE IS NULL)
  JOIN provider_tax_id TIN
    ON (PL.PROVIDER_SEQ_NUM = TIN.PROVIDER_SEQ_NUM)
    AND TIN.TAX_ID = :TaxIDNumber
  JOIN provider_identity ID
    ON (PL.PROVIDER_LOCATION_SEQ_NUM = ID.PROVIDER_LOCATION_SEQ_NUM)
    AND RTRIM(ID.IDENTITY_VALUE) = :OtherID
  JOIN provider_service SVC
    ON (PL.PROVIDER_LOCATION_SEQ_NUM = SVC.PROVIDER_LOCATION_SEQ_NUM
        AND TRIM(svc.PROVIDER_SERVICE_CODE) = :ServiceType)
  JOIN mco_provider MP
    ON (PL.PROVIDER_LOCATION_SEQ_NUM = MP.PROVIDER_LOCATION_SEQ_NUM
        AND MP.MCO_PLAN_ID = :MCOPlan)
WHERE pl.BUSINESS_FLAG = :BusinessFlag
  AND (pl.LAST_NAME_UPPER_CASE LIKE :ProviderName
       OR pl.BUSINESS_NAME_UPPER_CASE LIKE :ProviderName)
  AND pl.PROVIDER_TYPE_CODE = :ProviderType
  AND TRIM(a1.ADDRESS_LINE1) LIKE :StreetAddress
  AND TRIM(a1.CITY_NAME) = :CityName
  AND a1.STATE_CODE = :StateCode
  AND a1.ZIP_CODE = :ZipCode
  AND pl.PHONE_AREA_CODE = :PhoneAreaCode
  AND pl.PHONE_NUM LIKE :PhoneNumber
UNION
SELECT
  pl.PROVIDER_LOCATION_SEQ_NUM AS provLoc,
  pl.PRIMARY_LOCATION_FLAG     AS primaryLoc,
  pl.BUSINESS_FLAG,
  PL.BUSINESS_NAME,
  TYPE.CODE_DESCR              AS typeDescr,
  a1.ADDRESS_LINE1             AS physADDRESS,
  a1.CITY_NAME                 AS physCITY,
  a1.STATE_CODE                AS physState,
  a1.ZIP_CODE                  AS physZip,
  TRIM(T.REGISTERED_NAME)      AS fullNAME,
  CASE NVL(RTRIM(PL.PHONE_NUM) || ' ', ' ')
    WHEN ' ' THEN ''
    ELSE pl.PHONE_AREA_CODE || '.' || substr(pl.PHONE_NUM, 1, 3)
         || '.' || substr(pl.PHONE_NUM, 4)
  END AS PHONE
FROM provider_location PL
  JOIN provider_type_code TYPE
    ON PL.PROVIDER_TYPE_CODE = TYPE.CODE
  JOIN provider_tax_id T
    ON (PL.PROVIDER_SEQ_NUM = T.PROVIDER_SEQ_NUM)
  LEFT OUTER JOIN provider_address a1
    ON (pl.PROVIDER_LOCATION_SEQ_NUM = a1.PROVIDER_LOCATION_SEQ_NUM
        AND a1.PROVIDER_ADDRESS_CODE = 'PHY'
        AND a1.EXP_DATE IS NULL)
WHERE pl.BUSINESS_FLAG = :BusinessFlag
  AND t.REGISTERED_NAME_UPPER_CASE LIKE :ProviderName
ORDER BY fullNAME ASC
```

> When no business/person filter is requested the `WHERE` seed is `WHERE 1 = 1`.
> The caller truncates the result set at 300 rows (`If m_Providers.Count > 299 Then Exit For`).

## Operations and processes
- [Search provider locations](../operations/search-provider-locations.md)

## Business rules
- [BR-SRCH-001: Minimum search criteria](../business-rules.md#br-srch-001-at-least-one-search-field-is-required)
- [BR-SRCH-002: Numeric zip code](../business-rules.md#br-srch-002-zip-code-must-be-numeric)
- [BR-SRCH-003: Tax ID length limit](../business-rules.md#br-srch-003-tax-id-length-limit)
- [BR-SRCH-004: Business/person flag mapping](../business-rules.md#br-srch-004-businessperson-selector-maps-to-business-flag)
- [BR-SRCH-005: Registered name union match](../business-rules.md#br-srch-005-provider-name-search-also-matches-registered-names)
- [BR-SRCH-006: Sort name formatting](../business-rules.md#br-srch-006-sort-name-derivation)
- [BR-SRCH-007: Search result row cap](../business-rules.md#br-srch-007-search-results-capped-at-300-rows)
- [BR-SRCH-008: Search text normalization](../business-rules.md#br-srch-008-search-text-normalization)

## Sources
- SAIF.Business.View.Provider/clsProviderSearch.vb